﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
   public class PreDAL
    {
       SqlConnection str = new SqlConnection("Data Source=PC201609040948;Initial Catalog=TianTianDai_Db;Persist Security Info=True;User ID=sa;pwd=123456");
       /// <summary>
       /// 发标
       /// </summary>
       /// <returns></returns>
       public List<signs> ShowSigns()
       {
           str.Open();
           string sql = "select * from signs";
           SqlDataAdapter adap = new SqlDataAdapter(sql,str);
           DataTable ta = new DataTable();
           adap.Fill(ta);
           str.Close();
           List<signs> listsigns = new List<signs>();
           foreach (DataRow item in ta.Rows)
           {
               signs s = new signs();
               s.ChdMoney = Convert.ToDecimal(item["ChdMoney"]);
               s.EarningsId = Convert.ToInt32(item["EarningsId"]);
               s.Image = item["Image"].ToString();
               s.SendMoney = Convert.ToDecimal(item["SendMoney"]);
               s.SignDate = Convert.ToDateTime(item["SignDate"]);
               s.SignId = Convert.ToInt32(item["SignId"]);
               s.SignName = item["SignName"].ToString();
               s.SignTypeId = Convert.ToInt32(item["SignTypeId"]);
               s.Twapital = Convert.ToInt32(item["Twapital"]);
               s.Tyield = Convert.ToInt32(item["Tyield"]);
               s.DeadlineId = Convert.ToInt32(item["DeadlineId"]);

               listsigns.Add(s);
           }
           return listsigns;
       }
       /// <summary>
       /// 删除信息
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public int DeletePro(int id)
       {
           str.Open();
           string sql = "delete signs where SignId='" + id + "'";
           SqlCommand com = new SqlCommand(sql, str);
           var i = com.ExecuteNonQuery();
           return i;
       }
       /// <summary>
       /// 添加信息
       /// </summary>
       /// <param name="p"></param>
       /// <returns></returns>
       public int Sign_Add(Model.signs p)
       {
           str.Open();
           string sql = "insert into Project values('" + p.EarningsId + "','" + p.ChdMoney + "','" + p.SendMoney + "','" + p.SignDate + "','" + p.SignId + "','" + p.SignName + "','" + p.SignTypeId + "','" + p.Twapital + "','" + p.Tyield + "','" + p.DeadlineId + "','" + p.Image + "')";
           SqlCommand com = new SqlCommand(sql, str);
           var i = com.ExecuteNonQuery();
           return i;
       }
       /// <summary>
       /// 显示详细信息
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public signs ShowSignX(int id)
       {
           str.Open();
           string sql = "select * from signs where SignId='" + id + "'";
           SqlDataAdapter adap = new SqlDataAdapter(sql, str);
           DataTable ta = new DataTable();
           adap.Fill(ta);
           str.Close();
           List<signs> listsigns = new List<signs>();
           foreach (DataRow item in ta.Rows)
           {
               signs s = new signs();
               s.ChdMoney = Convert.ToDecimal(item["ChdMoney"]);
               s.EarningsId = Convert.ToInt32(item["EarningsId"]);
               s.Image = item["Image"].ToString();
               s.SendMoney = Convert.ToDecimal(item["SendMoney"]);
               s.SignDate = Convert.ToDateTime(item["SignDate"]);
               s.SignId = Convert.ToInt32(item["SignId"]);
               s.SignName = item["SignName"].ToString();
               s.SignTypeId = Convert.ToInt32(item["SignTypeId"]);
               s.Twapital = Convert.ToInt32(item["Twapital"]);
               s.DeadlineId = Convert.ToInt32(item["DeadlineId"]);

               listsigns.Add(s);
           }
           return listsigns.FirstOrDefault();
       }
       /// <summary>
       /// 修改信息
       /// </summary>
       /// <param name="data"></param>
       /// <returns></returns>
       public int UpdateSign(signs data)
       {
           str.Open();
           string sql = "update signs set  EarningsId='" + data.EarningsId + "',ChdMoney='" + data.ChdMoney + "',Image='" + data.Image + "',SendMoney='" + data.SendMoney + "',SignDate='" + data.SignDate + "',SignId='" + data.SignId + "',SignName='" + data.SignName + "',SignTypeId='" + data.SignTypeId + "',Twapital='" + data.Twapital + "',Tyield='" + data.Tyield + "',DeadlineId='"+data.DeadlineId+"', where ProjectId='" + data.SignId + "'";
           SqlCommand com = new SqlCommand(sql, str);
           int i = com.ExecuteNonQuery();
           return i;
       }
       /// <summary>
       /// 搜索
       /// </summary>
       /// <param name="pp"></param>
       /// <returns></returns>
       public List<signs> SeleteSign(string name)
       {
           str.Open();
           string sql = "selete ChdMoney,EarningsId,Image,SendMoney,SignDate,SignId,SignName,SignTypeId,Twapital,Tyield,DeadlineId from Project where SignName like '" + name + "'";
           SqlDataAdapter adap = new SqlDataAdapter(sql, str);
           DataTable ta = new DataTable();
           adap.Fill(ta);
           List<signs> listsigns = new List<signs>();
           foreach (DataRow item in ta.Rows)
           {
               signs s = new signs();
               s.ChdMoney = Convert.ToDecimal(item["ChdMoney"]);
               s.EarningsId = Convert.ToInt32(item["EarningsId"]);
               s.Image = item["Image"].ToString();
               s.SendMoney = Convert.ToDecimal(item["SendMoney"]);
               s.SignDate = Convert.ToDateTime(item["SignDate"]);
               s.SignId = Convert.ToInt32(item["SignId"]);
               s.SignName = item["SignName"].ToString();
               s.SignTypeId = Convert.ToInt32(item["SignTypeId"]);
               s.Twapital = Convert.ToInt32(item["Twapital"]);
               s.Tyield = Convert.ToInt32(item["Tyield"]);
               s.DeadlineId = Convert.ToInt32(item["DeadlineId"]);

               listsigns.Add(s);
           }
           return listsigns;
       }
    }
}
